1 <?php
2 /** A PHP class to access MySQL database with convenient methods
3 * in an object oriented way, and with a powerful debug system.\n
4 * Licence: LGPL \n
5 * Web site: http://slaout.linux62.org/
6 * @version 1.0
7 * @author Sébastien Laoût (slaout@linux62.org)
8 */
9 class DB
10 {
11 /** Put this variable to true if you want ALL queries to be debugged by default:
12 */
13 var $defaultDebug = false;
14
15 /** INTERNAL: The start time, in miliseconds.
16 */
17 var $mtStart;
18 /** INTERNAL: The number of executed queries.
19 */
20 var $nbQueries;
21 /** INTERNAL: The last result ressource of a query().
22 */
23 var $lastResult;
24
25 /** Connect to a MySQL database to be able to use the methods below.
26 */
27 function DB($base, $server, $user, $pass)
28 {
29 $this->mtStart = $this->getMicroTime();
30 $this->nbQueries = 0;
31 $this->lastResult = NULL;
32 mysql_connect($server, $user, $pass) or die('Server connexion not possible.');
33 mysql_select_db($base) or die('Database connexion not possible.');
34 }
35
36 /** Query the database.
37 * @param $query The query.
38 * @param $debug If true, it output the query and the resulting table.
39 * @return The result of the query, to use with fetchNextObject().
40 */
41 function query($query, $debug = -1)
42 {
43 $this->nbQueries++;
44 $this->lastResult = mysql_query($query) or $this->debugAndDie($query);
45
46 $this->debug($debug, $query, $this->lastResult);
47
48 return $this->lastResult;
49 }
50 /** Do the same as query() but do not return nor store result.\n
51 * Should be used for INSERT, UPDATE, DELETE...
52 * @param $query The query.
53 * @param $debug If true, it output the query and the resulting table.
54 */
55 function execute($query, $debug = -1)
56 {
57 $this->nbQueries++;
58 mysql_query($query) or $this->debugAndDie($query);
59
60 $this->debug($debug, $query);
61 }
62 /** Convenient method for mysql_fetch_object().
63 * @param $result The ressource returned by query(). If NULL, the last result returned by query() will be used.
64 * @return An object representing a data row.
65 */
66 function fetchNextObject($result = NULL)
67 {
68 if ($result == NULL)
69 $result = $this->lastResult;
70
71 if ($result == NULL || mysql_num_rows($result) < 1)
72 return NULL;
73 else
74 return mysql_fetch_object($result);
75 }
76 /** Get the number of rows of a query.
77 * @param $result The ressource returned by query(). If NULL, the last result returned by query() will be used.
78 * @return The number of rows of the query (0 or more).
79 */
80 function numRows($result = NULL)
81 {
82 if ($result == NULL)
83 return mysql_num_rows($this->lastResult);
84 else
85 return mysql_num_rows($result);
86 }
87 /** Get the result of the query as an object. The query should return a unique row.\n
88 * Note: no need to add "LIMIT 1" at the end of your query because
89 * the method will add that (for optimisation purpose).
90 * @param $query The query.
91 * @param $debug If true, it output the query and the resulting row.
92 * @return An object representing a data row (or NULL if result is empty).
93 */
94 function queryUniqueObject($query, $debug = -1)
95 {
96 $query = "$query LIMIT 1";
97
98 $this->nbQueries++;
99 $result = mysql_query($query) or $this->debugAndDie($query);
100
101 $this->debug($debug, $query, $result);
102
103 return mysql_fetch_object($result);
104 }
105 /** Get the result of the query as value. The query should return a unique cell.\n
106 * Note: no need to add "LIMIT 1" at the end of your query because
107 * the method will add that (for optimisation purpose).
108 * @param $query The query.
109 * @param $debug If true, it output the query and the resulting value.
110 * @return A value representing a data cell (or NULL if result is empty).
111 */
112 function queryUniqueValue($query, $debug = -1)
113 {
114 $query = "$query LIMIT 1";
115
116 $this->nbQueries++;
117 $result = mysql_query($query) or $this->debugAndDie($query);
118 $line = mysql_fetch_row($result);
119
120 $this->debug($debug, $query, $result);
121
122 return $line[0];
123 }
124 /** Get the maximum value of a column in a table, with a condition.
125 * @param $column The column where to compute the maximum.
126 * @param $table The table where to compute the maximum.
127 * @param $where The condition before to compute the maximum.
128 * @return The maximum value (or NULL if result is empty).
129 */
130 function maxOf($column, $table, $where)
131 {
132 return $this->queryUniqueValue("SELECT MAX(`$column`) FROM `$table` WHERE $where");
133 }
134 /** Get the maximum value of a column in a table.
135 * @param $column The column where to compute the maximum.
136 * @param $table The table where to compute the maximum.
137 * @return The maximum value (or NULL if result is empty).
138 */
139 function maxOfAll($column, $table)
140 {
141 return $this->queryUniqueValue("SELECT MAX(`$column`) FROM `$table`");
142 }
143 /** Get the count of rows in a table, with a condition.
144 * @param $table The table where to compute the number of rows.
145 * @param $where The condition before to compute the number or rows.
146 * @return The number of rows (0 or more).
147 */
148 function countOf($table, $where)
149 {
150 return $this->queryUniqueValue("SELECT COUNT(*) FROM `$table` WHERE $where");
151 }
152 /** Get the count of rows in a table.
153 * @param $table The table where to compute the number of rows.
154 * @return The number of rows (0 or more).
155 */
156 function countOfAll($table)
157 {
158 return $this->queryUniqueValue("SELECT COUNT(*) FROM `$table`");
159 }
160 /** Internal function to debug when MySQL encountered an error,
161 * even if debug is set to Off.
162 * @param $query The SQL query to echo before diying.
163 */
164 function debugAndDie($query)
165 {
166 $this->debugQuery($query, "Error");
167 die("<p style=\"margin: 2px;\">".mysql_error()."</p></div>");
168 }
169 /** Internal function to debug a MySQL query.\n
170 * Show the query and output the resulting table if not NULL.
171 * @param $debug The parameter passed to query() functions. Can be boolean or -1 (default).
172 * @param $query The SQL query to debug.
173 * @param $result The resulting table of the query, if available.
174 */
175 function debug($debug, $query, $result = NULL)
176 {
177 if ($debug === -1 && $this->defaultDebug === false)
178 return;
179 if ($debug === false)
180 return;
181
182 $reason = ($debug === -1 ? "Default Debug" : "Debug");
183 $this->debugQuery($query, $reason);
184 if ($result == NULL)
185 echo "<p style=\"margin: 2px;\">Number of affected rows: ".mysql_affected_rows()."</p></div>";
186 else
187 $this->debugResult($result);
188 }
189 /** Internal function to output a query for debug purpose.\n
190 * Should be followed by a call to debugResult() or an echo of "</div>".
191 * @param $query The SQL query to debug.
192 * @param $reason The reason why this function is called: "Default Debug", "Debug" or "Error".
193 */
194 function debugQuery($query, $reason = "Debug")
195 {
196 $color = ($reason == "Error" ? "red" : "orange");
197 echo "<div style=\"border: solid $color 1px; margin: 2px;\">".
198 "<p style=\"margin: 0 0 2px 0; padding: 0; background-color: #DDF;\">".
199 "<strong style=\"padding: 0 3px; background-color: $color; color: white;\">$reason:</strong> ".
200 "<span style=\"font-family: monospace;\">".htmlentities($query)."</span></p>";
201 }
202 /** Internal function to output a table representing the result of a query, for debug purpose.\n
203 * Should be preceded by a call to debugQuery().
204 * @param $result The resulting table of the query.
205 */
206 function debugResult($result)
207 {
208 echo "<table border=\"1\" style=\"margin: 2px;\">".
209 "<thead style=\"font-size: 80%\">";
210 $numFields = mysql_num_fields($result);
211 // BEGIN HEADER
212 $tables = array();
213 $nbTables = -1;
214 $lastTable = "";
215 $fields = array();
216 $nbFields = -1;
217 while ($column = mysql_fetch_field($result)) {
218 if ($column->table != $lastTable) {
219 $nbTables++;
220 $tables[$nbTables] = array("name" => $column->table, "count" => 1);
221 } else
222 $tables[$nbTables]["count"]++;
223 $lastTable = $column->table;
224 $nbFields++;
225 $fields[$nbFields] = $column->name;
226 }
227 for ($i = 0; $i <= $nbTables; $i++)
228 echo "<th colspan=".$tables[$i]["count"].">".$tables[$i]["name"]."</th>";
229 echo "</thead>";
230 echo "<thead style=\"font-size: 80%\">";
231 for ($i = 0; $i <= $nbFields; $i++)
232 echo "<th>".$fields[$i]."</th>";
233 echo "</thead>";
234 // END HEADER
235 while ($row = mysql_fetch_array($result)) {
236 echo "<tr>";
237 for ($i = 0; $i < $numFields; $i++)
238 echo "<td>".htmlentities($row[$i])."</td>";
239 echo "</tr>";
240 }
241 echo "</table></div>";
242 $this->resetFetch($result);
243 }
244 /** Get how many time the script took from the begin of this object.
245 * @return The script execution time in seconds since the
246 * creation of this object.
247 */
248 function getExecTime()
249 {
250 return round(($this->getMicroTime() - $this->mtStart) * 1000) / 1000;
251 }
252 /** Get the number of queries executed from the begin of this object.
253 * @return The number of queries executed on the database server since the
254 * creation of this object.
255 */
256 function getQueriesCount()
257 {
258 return $this->nbQueries;
259 }
260 /** Go back to the first element of the result line.
261 * @param $result The resssource returned by a query() function.
262 */
263 function resetFetch($result)
264 {
265 if (mysql_num_rows($result) > 0)
266 mysql_data_seek($result, 0);
267 }
268 /** Get the id of the very last inserted row.
269 * @return The id of the very last inserted row (in any table).
270 */
271 function lastInsertedId()
272 {
273 return mysql_insert_id();
274 }
275 /** Close the connexion with the database server.\n
276 * It's usually unneeded since PHP do it automatically at script end.
277 */
278 function close()
279 {
280 mysql_close();
281 }
282
283 /** Internal method to get the current time.
284 * @return The current time in seconds with microseconds (in float format).
285 */
286 function getMicroTime()
287 {
288 list($msec, $sec) = explode(' ', microtime());
289 return floor($sec / 1000) + $msec;
290 }
291 } // class DB
292 ?>
